Script to find Whether patches is applied in Oracle EBS

闲来无事的时候，写个一个PL/SQL脚本，用来查找Oracle Appplications中是否已经apply过一系列patch。在需要确认一堆补丁的状态时，是比较有用的。脚本使用PL/SQL中的varray数据类型存储需要查找的补丁号。
脚本内容如下：

sqlDECLARE
  V_COUNT     NUMBER := 0;
  V_PATCH_NUM VARCHAR2(20);
  TYPE PATCH_NUMBER_TYPE IS VARRAY(10) OF VARCHAR2(20);
  PATCH_NUMBER PATCH_NUMBER_TYPE := PATCH_NUMBER_TYPE('7303030',
                                                      '9062910',
                                                      '8919489',
                                                      '9868229',
                                                      '10163753',
                                                      '11071569',
                                                      '9738085',
                                                      '9852070',
                                                      '12686610',
                                                      '6400501');
BEGIN
  FOR I IN 1 .. PATCH_NUMBER.COUNT LOOP
    BEGIN
      SELECT distinct 'APPLIED'
        INTO V_PATCH_NUM
        FROM AD_BUGS
       WHERE BUG_NUMBER = PATCH_NUMBER(I);
      DBMS_OUTPUT.PUT_LINE(PATCH_NUMBER(I) || ' is ' || V_PATCH_NUM);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        V_PATCH_NUM := 'NOT APPLIED';
        DBMS_OUTPUT.PUT_LINE(PATCH_NUMBER(I) || ' is ' || V_PATCH_NUM);
    END;

  END LOOP;

END;

运行的结果可能如下面所示：

sql7303030 is NOT APPLIED
9062910 is NOT APPLIED
8919489 is NOT APPLIED
9868229 is NOT APPLIED
10163753 is NOT APPLIED
11071569 is NOT APPLIED
9738085 is NOT APPLIED
9852070 is NOT APPLIED
12686610 is APPLIED
6400501 is NOT APPLIED